﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingCoreCompetencyDAL : ITrainingCoreCompetencyDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable LoadCoreCompetency()
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT CONCAT(a.core_competency_name,' : ',b.level_name) AS competency_name,b.competency_level_seq AS proficiency_seq "
                         + " FROM hrm_perf_core_competency a "
                         + " INNER JOIN hrm_perf_competency_level b ON b.core_competency_seq = a.core_competency_seq ";
                       
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadCompentencyLevel()
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT competency_level_seq, level_name FROM hrm_perf_competency_level "
                         + " WHERE record_status not like @status ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Search(int? intCurrCompetencySeq, int? intCoreCompentencyTypeSeq, int? intCompentencyLevel, int? intProficiencySeq, int? ID)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@training_cur_seq", ID);

                strSQL = " SELECT a.curriculum_competency_seq, a.training_cur_seq, a.competency_type_seq,"
                        +" b.type_name, a.competency_ref_seq, ISNULL(c.proficiency_name, d.level_name) proficiency_name"
                        +" FROM hrd_training_curriculum_competency a "
                        +" LEFT JOIN hrm_perf_competency_type b ON a.competency_type_seq = b.competency_type_seq"
                        +" LEFT JOIN hrm_perf_proficiency c ON a.competency_ref_seq = c.proficiency_seq"
                        +" LEFT JOIN hrm_perf_competency_level d ON a.competency_ref_seq = d.competency_level_seq"
                        + " WHERE a.record_status not like @status AND training_cur_seq = @training_cur_seq";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int? intCoreCompentencyTypeSeq, int? ID, int? intCompentencyLevel, int? CreatBy, int? intCurrCompetencySeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@competency_type_seq", intCoreCompentencyTypeSeq);
                dbManager.AddParameters(1, "@competency_ref_seq", intCompentencyLevel);
                dbManager.AddParameters(2, "@updated_by", CreatBy);
                dbManager.AddParameters(3, "@curriculum_competency_seq", intCurrCompetencySeq);
                dbManager.AddParameters(4, "@training_cur_seq", ID);

                strSQL = " UPDATE hrd_training_curriculum_competency"
                         + " SET competency_type_seq = @competency_type_seq,"
                         + " competency_ref_seq = @competency_ref_seq,"
                         + " updated_by = @updated_by,"
                         + " updated_when = GETDATE()"
                         + " WHERE curriculum_competency_seq = @curriculum_competency_seq"
                         + " AND training_cur_seq = @training_cur_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update1(int? intCoreCompentencyTypeSeq, int? ID, int? intProficiencySeq, int? CreatBy, int? intCurrCompetencySeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@competency_type_seq", intCoreCompentencyTypeSeq);
                dbManager.AddParameters(1, "@competency_ref_seq", intProficiencySeq);
                dbManager.AddParameters(2, "@updated_by", CreatBy);
                dbManager.AddParameters(3, "@curriculum_competency_seq", intCurrCompetencySeq);
                dbManager.AddParameters(4, "@training_cur_seq", ID);

                strSQL = " UPDATE hrd_training_curriculum_competency"
                         + " SET competency_type_seq = @competency_type_seq,"
                         + " competency_ref_seq = @competency_ref_seq,"
                         + " updated_by = @updated_by,"
                         + " updated_when = GETDATE()"
                         + " WHERE curriculum_competency_seq = @curriculum_competency_seq"
                         + " AND training_cur_seq = @training_cur_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int? intCurrCompetencySeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@curriculum_competency_seq", intCurrCompetencySeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " UPDATE hrd_training_curriculum_competency"
                        +" SET record_status = @status "
                        +" WHERE curriculum_competency_seq = @curriculum_competency_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(int? intCoreCompentencyTypeSeq, int? ID, int? intCompentencyLevel, int? CreatBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@competency_type_seq", intCoreCompentencyTypeSeq);
                dbManager.AddParameters(1, "@training_cur_seq", ID);
                dbManager.AddParameters(2, "@competency_ref_seq", intCompentencyLevel);
                dbManager.AddParameters(3, "@created_by", CreatBy);

                strSQL = "INSERT INTO hrd_training_curriculum_competency "
                         + " (competency_type_seq, "
                         + " training_cur_seq, "
                         + " competency_ref_seq,"
                         + " created_by,"
                         + " created_when)"
                         + " VALUES"
                         + "( @competency_type_seq,"
                         + " @training_cur_seq, "
                         + " @competency_ref_seq,"
                         +" @created_by, "
                         +" GETDATE())";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert1(int? intCoreCompentencyTypeSeq, int? ID, int? intProficiencySeq, int? CreatBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@competency_type_seq", intCoreCompentencyTypeSeq);
                dbManager.AddParameters(1, "@training_cur_seq", ID);
                dbManager.AddParameters(2, "@competency_ref_seq", intProficiencySeq);
                dbManager.AddParameters(3, "@created_by", CreatBy);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);

                strSQL = "INSERT INTO hrd_training_curriculum_competency "
                         + " (competency_type_seq, "
                         + " training_cur_seq, "
                         + " competency_ref_seq,"
                         + " created_by,"
                         + " created_when, record_status)"
                         + " VALUES"
                         + "( @competency_type_seq,"
                         + " @training_cur_seq, "
                         + " @competency_ref_seq,"
                         + " @created_by, "
                         + " GETDATE(), @record_status)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadCoreCompetencyType()
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT competency_type_seq, type_name FROM hrm_perf_competency_type"
                          + " WHERE record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool CheckValueSubmit(int? intCurrCompetencySeq)
        {
            string strSQL;

            bool result = true;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@curriculum_competency_seq", intCurrCompetencySeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT curriculum_competency_seq, training_cur_seq, competency_type_seq, "
                         +" competency_ref_seq, created_by "
                         + " FROM hrd_training_curriculum_competency"
                         + " WHERE record_status = @status";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    result = true;
                    break;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;
        }

        public bool CheckUnique(int? ID, int? intCoreCompentencyTypeSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@training_cur_seq", ID);
                dbManager.AddParameters(1, "@competency_type_seq", intCoreCompentencyTypeSeq);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT competency_type_seq, competency_ref_seq FROM hrd_training_curriculum_competency"
                         + " WHERE record_status not like @status"
                         + " AND training_cur_seq = @training_cur_seq"
                         + " AND competency_type_seq = @competency_type_seq ";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    return true;
                }
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return false;
        }
    }
}